﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wuqi.Webdiyer;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using MyDAL;

namespace MyClass.PagerHelper
{
    /// <summary>
    /// sql分页帮助类
    /// </summary>
    public class SqlPagerHelper
    {
        #region 分页存储过程
        /*分页存储过程
         * set statistics time on --耗时
        ALTER  Procedure [dbo].[prGetRecordByPage]
        (
        @pagesize int, --每页的记录条数
        @pagenum int, --当前页面  1为第一页
        @QuerySql varchar(1000),--部分查询字符串,如 '  FROM t_Company where...'
        @keyId varchar(500), --列ID
        @keyIdOrder varchar(500), --desc/asc
        @order varchar(50),--order by [clos] desc/asc
        @clos varchar(50)--orderseq,companycode... 

        )
        AS
        Begin 
        Declare @SqlText AS Varchar(1000)
        declare @SqlText1 AS Varchar(1000)
        set @SqlText=' SELECT * '
        +' FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@keyId+' '+@keyIdOrder+', '+@keyId+') AS rownum,'+@clos +' '+ @QuerySql+' ) AS D '  
        +' WHERE rownum BETWEEN '+Cast((@pagenum-1)*(@pagesize+1) as varchar(10))+' AND '+Cast(@pagenum*@pagesize as varchar(10))+ '  '+@order +''
        Exec(@SqlText)
        set @SqlText1=' select count(*) as totalCount '+@QuerySql
        Exec(@SqlText1)
        end
        */
        #endregion

        #region  私有成员
        private string _DbUrl = "";
        private int _totalCount;
        private int _cur = 0;
        private int _size=0;
        private string _querySql;
        private string _keyField;
        private string _keyFieldOrder;
        private string _order;
        private string _clos;
        private string _procName;
        private DataTable _dt;
        private string _tableId;
        private string _tableName;
        private string _condition;

        MyDAL.Enumeration.ConnStringType _type;
        private string _conn;
        private IDataBaseAdvance idba;
        #endregion

        #region 属性
        /// <summary>
        /// 当前页
        /// </summary>
        public int Cur
        {
            get { return _cur; }
            set { _cur = value; }
        }
        /// <summary>
        /// 页的大小
        /// </summary>
        public int Size
        {
            get { return _size; }
        }

        /// <summary>
        /// 数据内容
        /// </summary>
        public  DataTable Dt
        {
            get { return _dt; }
        }
        /// <summary>
        /// 总条数
        /// </summary>
        public  int TotalCount
        {
            get { return _totalCount; }
        }

        #endregion

        /// <summary>
        /// sql分页初始化
        /// </summary>
        /// <param name="cur">当前页(1为第一页)</param>
        /// <param name="size">页大小</param>
        /// <param name="querySql"> [From Test where ...] | [Form Test]</param>
        /// <param name="keyField">排序列 [生成序号的列[rownum]]</param>
        /// <param name="keyField">keyFieldOrder [asc/desc] [生成序号的列[rownum]]</param>
        /// <param name="order">排序方式 [order by [clos] [desc/asc]] | Empty</param>
        /// <param name="clos">显示列 [*/clos1,clos2...] </param>
        public SqlPagerHelper(int size, string querySql, string keyField, string keyFieldOrder, string orderParam, string closParam,string connstr,MyDAL.Enumeration.ConnStringType t)
        {
            idba=new SQLDataBase(connstr,t);
            this._size = size;
            this._querySql = querySql;
            this._keyField = keyField;
            this._keyFieldOrder = keyFieldOrder;
            this._order = orderParam;
            this._clos = closParam;
            this._conn = connstr;
        }
        /// <summary>
        /// sql分页初始化
        /// </summary>
        /// <param name="cur">当前页(1为第一页)</param>
        /// <param name="size">页大小</param>
        /// <param name="querySql"> [From Test where ...] | [Form Test]</param>
        /// <param name="keyField">排序列 [生成序号的列[rownum]]</param>
        /// <param name="keyField">keyFieldOrder [asc/desc] [生成序号的列[rownum]]</param>
        /// <param name="order">排序方式 [order by [clos] [desc/asc]] | Empty</param>
        /// <param name="clos">显示列 [*/clos1,clos2...] </param>
        public SqlPagerHelper(string procName, int size, string querySql, string keyField, string keyFieldOrder, string orderParam, string closParam, string connstr, MyDAL.Enumeration.ConnStringType t)
        {
            idba = new SQLDataBase(connstr, t);
            this._procName = procName;
            this._size = size;
            this._querySql = querySql;
            this._keyField = keyField;
            this._keyFieldOrder = keyFieldOrder;
            this._order = orderParam;
            this._clos = closParam;
            this._type = t;
            this._conn = connstr;
        }
        /// <summary>
        /// 非sql2005以上版本使用
        /// </summary>
        /// <param name="cur">当前页(1为第一页)</param>
        /// <param name="clos">显示的列clo1,clo2.. 或.*</param>
        /// <param name="tableId">表ID</param>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件 [clos1=value]|Empty</param>
        /// <param name="size">页大小</param>
        /// <param name="orderParam">排序 [order by [clos] desc/asc] | Empty</param>
        public SqlPagerHelper(string closParam, string tableId, string tableName, string condition, int size, string orderParam, string connstr, MyDAL.Enumeration.ConnStringType t)
        {
            idba = new SQLDataBase(connstr, t);
            this._clos = closParam;
            this._tableId = tableId;
            this._tableName = tableName;
            this._condition = condition;
            this._size = size;
            this._order = orderParam;
            this._type = t;
            this._conn = connstr;
        }

        #region sql分页2005
        /// <summary>
        /// sql分页2005
        /// </summary>
        /// <param name="cur">当前页</param>
        /// <returns>当前面内容</returns>
        public DataSet GetCurrentPageData(int cur)
        {
            if (!string.IsNullOrEmpty(this._procName))
            {
                this._cur = cur;
                string sql = this._procName;
               
                SqlParameter[] paramList =  MyClass.CommandHelper.SQLProcParamBuilder.GetSQLParams(
                    new string[]{"@pagesize","@pagenum","@QuerySql","@keyId","@keyIdOrder","@order","@clos"},
                    new object[]{this._size,this._cur,this._querySql,this._keyField,this._keyFieldOrder,this._order,this._clos}
                );
                DataSet ds = idba.GetDataSet(sql, paramList);
                _totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                _dt = ds.Tables[0];
                return ds;
            }
            else if (!string.IsNullOrEmpty(this._tableId))
            {
                this._cur = cur;
                string sql = GetSQLCommandString();
                DataSet ds = idba.GetDataSet(sql);
                _totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                _dt = ds.Tables[0];
                return ds;
            }
            else
            {
                this._cur = cur;
                StringBuilder sql = new StringBuilder();
                sql.Append(" DECLARE @pagenum AS INT, @pagesize AS INT ");
                sql.Append(" SET @pagenum = " + this._cur);
                sql.Append(" SET @pagesize = " + this._size);
                sql.Append(" SELECT * ");
                sql.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + this._keyField + " " + this._keyFieldOrder + ", " + this._keyField + ") AS ");
                sql.Append(" rownum, ");
                sql.Append(this._clos + " " + this._querySql + ") AS D  ");
                sql.Append(" WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize  " + this._order);
                sql.Append(" select count(*)as totalCount " + this._querySql);
                DataSet ds = idba.GetDataSet(sql.ToString());
                _totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                _dt = ds.Tables[0];
                return ds;
            }

        }
        /// <summary>
        /// sql分页2005
        /// </summary>
        /// <param name="cur">当前页</param>
        /// <returns>当前面内容</returns>
        public DataSet GetCurrentPageData(int cur,out int totalCount)
        {
            if (!string.IsNullOrEmpty(this._procName))
            {
                this._cur = cur;
                string sql = this._procName;

                SqlParameter[] paramList = MyClass.CommandHelper.SQLProcParamBuilder.GetSQLParams(
                    new string[] { "@pagesize", "@pagenum", "@QuerySql", "@keyId", "@keyIdOrder", "@order", "@clos" },
                    new object[] { this._size, this._cur, this._querySql, this._keyField, this._keyFieldOrder, this._order, this._clos }
                );
                DataSet ds = idba.GetDataSet(sql, paramList);
                this._totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                totalCount = this._totalCount;
                this._dt = ds.Tables[0];
                return ds;
            }
            else if (!string.IsNullOrEmpty(this._tableId))
            {
                this._cur = cur;
                string sql = GetSQLCommandString();
                DataSet ds = idba.GetDataSet(sql);
                _totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                _dt = ds.Tables[0];
                totalCount = this._totalCount;
                return ds;
            }
            else
            {
                this._cur = cur;
                StringBuilder sql = new StringBuilder();
                sql.Append(" DECLARE @pagenum AS INT, @pagesize AS INT ");
                sql.Append(" SET @pagenum = " + this._cur);
                sql.Append(" SET @pagesize = " + this._size);
                sql.Append(" SELECT * ");
                sql.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + this._keyField + " " + this._keyFieldOrder + ", " + this._keyField + ") AS ");
                sql.Append(" rownum, ");
                sql.Append(this._clos + " " + this._querySql + ") AS D  ");
                sql.Append(" WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize  " + this._order);
                sql.Append(" select count(*)as totalCount " + this._querySql);
                DataSet ds = idba.GetDataSet(sql.ToString());
                _totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                _dt = ds.Tables[0];
                totalCount = this._totalCount;
                return ds;
            }

        }
        #endregion 

        /// <summary>
        /// 返回SQL分页字符窜
        /// </summary>
        /// <returns>返回SQL分页字符窜</returns>
        public string GetSQLCommandString()
        {
            string s=string .Empty ;
            if (!string.IsNullOrEmpty(this._procName))
            {
                s = this._procName;
            }
            else if (!string.IsNullOrEmpty(this._tableId))
            {
                string sql = string.Empty;
                string sql1 = string.Empty;
                if (this._condition == string.Empty)
                {
                    if (this._cur == 1)
                    {
                        sql = string.Format("select top {0} {1} from {2} order by {3} asc", this._size, this._clos, this._tableName, this._tableId);
                    }
                    else
                    {
                        sql = string.Format("select top {0} {1} from {2} where {3} > ( select max({3}) from  ( select top {7} {1} from {2} order by {3} asc) as t  )   {6} ",
                          this._size * this._cur, this._clos, this._tableName, this._tableId, this._cur, this._condition, this._order, this._size * (this._cur - 1));
                    }
                    sql1 = string.Format("select count(*) from {0}", this._tableName);
                }
                else
                {
                    if (this._cur == 1)
                    {
                        sql = string.Format("select top {0} {1} from {2} order by {3} asc", this._size, this._clos, this._tableName, this._tableId);
                    }
                    else
                    {
                        sql = string.Format("select top {0} {1} from {2} where {3} > ( select max({3}) from  ( select top {7} {1} from {2} order by {3} asc ) as t ) and  {5} {6} ",
                           this._size * this._cur, this._clos, this._tableName, this._tableId, this._cur, this._condition, this._order, this._size * (this._cur - 1));
                    }
                    sql1 = string.Format("select count(*) from {0} where {1} ", this._tableName, this._condition);
                }
                s = sql + sql1;
            }
            else
            {
                StringBuilder sql = new StringBuilder();
                sql.Append(" DECLARE @pagenum AS INT, @pagesize AS INT ");
                sql.Append(" SET @pagenum = " + this._cur);
                sql.Append(" SET @pagesize = " + this._size);
                sql.Append(" SELECT * ");
                sql.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY " + this._keyField + " " + this._keyFieldOrder + ", " + this._keyField + ") AS ");
                sql.Append(" rownum, ");
                sql.Append(this._clos + " " + this._querySql + ") AS D  ");
                sql.Append(" WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize  " + this._order);
                sql.Append(" select count(*)as totalCount " + this._querySql);
                s = sql.ToString();
            }

            return s;
        }
    }
}
